IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].RptFinancialAgingReport') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].RptFinancialAgingReport
go
-- =======================================================================
-- Procedure Name: dbo.RptFinancialAgingReport
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 10/03/2008
--
-- Description: Gets Count of Financial Deliverables based on Deliverable Due Date
--
-- Parameters:  
--     @StartDate                         
--     @EndDate         
--     @DeliverableType
--     @DeliverableStatus                  
--     @Institution                       
--     @Department                        
--     @Unit                              
--     @Subcontractor                     
--     @SubcontractSpecialist  
--     @GroupBy
--     @ReportDate
--     @DomainUserId                      
--
-- Returns:     INT, 0=Success, -1=Error
--
-- RptFinancialAgingReport NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'ORG',120,NULL,11055 
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 04/20/09	RM	Created based on RptSubcontractDeliverableReport
-- =======================================================================

CREATE PROCEDURE dbo.RptFinancialAgingReport
     (@StartDate                        DATETIME     = NULL,  
     @EndDate                           DATETIME     = NULL,
     @Institution                       VARCHAR(100) = NULL,
     @Department                        VARCHAR(100) = NULL,
     @Unit                              VARCHAR(100) = NULL,
     @FundNumber                        VARCHAR(1000) = NULL,
     @PrincipalInvestigator             VARCHAR(100) = NULL, 
     @Sponsor                           VARCHAR(100) = NULL,
     @OriginatingSponsor                VARCHAR(100) = NULL,
     @Subcontractor                     VARCHAR(100) = NULL,
     @RMStaffMember						VARCHAR(100) = NULL,
     @DeliverableType                   VARCHAR(100) = NULL,
     @DeliverableStatus					VARCHAR(5000) = NULL,
     @GroupBy                           VARCHAR(20) = NULL,
     @GroupType							INT = NULL,
     @ReportDate						DATETIME = NULL, 
     @DomainUserId                      INT )

WITH RECOMPILE                                          
AS
BEGIN
     -- Initial Settings
     SET NOCOUNT ON
     SET ANSI_WARNINGS OFF

     IF @ReportDate IS NULL 
     BEGIN
	 SET @ReportDate = GETDATE()
     END

     -- Declare Local Variables
     DECLARE   @Error              INT,
               @RowCount           INT,
               @ReturnCode         INT
               
     
     DECLARE   @Proposals TABLE (
				Institution              VARCHAR(50)    NULL,
				FolderNumber             VARCHAR(12)    NULL,
				InfoEdPropNumber         VARCHAR(50)    NULL,
				PrincipalInvestigator    VARCHAR(64)    NULL,
				ChiefCode                VARCHAR(4)     NULL,
				SponsorName              VARCHAR(75)    NULL,
				SecondarySponsor         VARCHAR(75)    NULL,
				SponsorAgreementID		 VARCHAR(35)	NULL, 
				AwardPeriod				 INT 		NULL, 
				AwardStartDate			 DATETIME	NULL, 
				AwardPeriodStatus		 VARCHAR(80)	NULL, 
				PostAwardManagerName	 VARCHAR(100)   NULL, 
				RMStaffMember			 VARCHAR(100)   NULL, 
				NOGAUploadDate			 DATETIME	NULL, 
				PSProjectNumber			 VARCHAR(50)	NULL, 
				ProjectStartDate		 DATETIME	NULL, 
				ProjectEndDate			 DATETIME	NULL, 
				InstrumentType           VARCHAR(50)    NULL,
				DeliverableDueDate	 DATETIME	NULL, 
				DeliverableName		 VARCHAR(50)	NULL, 
				ReportCategory		 VARCHAR(80)	NULL, 
				ReportStatus		 VARCHAR(80)	NULL, 
				DeliverableComments	 VARCHAR(2000)	NULL, 
				SubContractorName        VARCHAR(75)    NULL,
          		ReportId                 BIGINT         NULL,
           		REPORTCAT_CODE           VARCHAR(50)    NULL,
        		REPORTSTAT_CODE          VARCHAR(50)    NULL,
        		AgreementId              INT            NULL,
        		InstitutionId            INT            NULL,
        		DepartmentId             INT            NULL,
        		UnitId                   INT            NULL,
        		SponsorId                INT            NULL,
        		PrincipalInvestigatorId  INT            NULL,
        		SubcontractsYN           CHAR(1)        NULL,
        		SubContractorId          INT            NULL)

     -- Let Detail Proc do the work.
     INSERT    @Proposals
            (Institution, 
	    	FolderNumber,
        	InfoEdPropNumber,
        	PrincipalInvestigator,
        	ChiefCode,
        	SponsorName,
       		SecondarySponsor,
            SponsorAgreementID, 
			AwardPeriod, 
			AwardStartDate, 
			AwardPeriodStatus, 
			PostAwardManagerName, 
			RMStaffMember, 
			NOGAUploadDate, 
			PSProjectNumber, 
			ProjectStartDate, 
			ProjectEndDate, 
			InstrumentType,
            DeliverableDueDate, 
			DeliverableName, 
			ReportCategory, 
			ReportStatus, 
			DeliverableComments, 
			SubContractorName,
        	ReportId,
        	REPORTCAT_CODE,
        	REPORTSTAT_CODE,
        	AgreementId,
        	InstitutionId,
        	DepartmentId,
			UnitId,
        	SponsorId,
        	PrincipalInvestigatorId,
           	SubcontractsYN,
           	SubContractorId)
			
     EXECUTE @ReturnCode = dbo.RptFinancialAgingDetailReport
                              @StartDate               = @StartDate,
                              @EndDate                 = @EndDate,
                              @Institution             = @Institution,
                              @Department              = @Department,
                              @Unit                    = @Unit,
                              @FundNumber              = @FundNumber,
                              @PrincipalInvestigator   = @PrincipalInvestigator, 
                              @Sponsor                 = @Sponsor,
                              @OriginatingSponsor      = @OriginatingSponsor,
                              @Subcontractor           = @Subcontractor,
                              @RMStaffMember   	       = @RMStaffMember,
							  @DeliverableType         = @DeliverableType,
							  @DeliverableStatus       = @DeliverableStatus,   -- Get All
							  @GroupBy					= NULL,
							  @GroupType				= @GroupType,
							  @SelectedHeaderStatus    = NULL,
							  @DomainUserId            = @DomainUserId
                              
     -- check for errors
     SELECT @Error=@@ERROR
     IF @Error != 0 OR @ReturnCode != 0
     BEGIN
          RETURN -1
     END

     -- We have to return some search parameter names so they can display in the headers.
     -- Only if searching on one and only one value
     IF @Institution IS NOT NULL AND CHARINDEX(',',@Institution) = 0
          SELECT    TOP 1 @Institution = Institution
          FROM      @Proposals
     ELSE
          SELECT    @Institution = NULL
     
     IF @PrincipalInvestigator IS NOT NULL AND CHARINDEX(',',@PrincipalInvestigator) = 0
          SELECT    TOP 1 @PrincipalInvestigator = PrincipalInvestigator
          FROM      @Proposals
     ELSE
          SELECT    @PrincipalInvestigator = NULL
     
     IF @OriginatingSponsor IS NOT NULL AND CHARINDEX(',',@OriginatingSponsor) = 0
          SELECT    TOP 1 @OriginatingSponsor = SecondarySponsor
          FROM      @Proposals
     ELSE
          SELECT    @OriginatingSponsor = NULL

     IF @Sponsor IS NOT NULL AND CHARINDEX(',',@Sponsor) = 0
          SELECT    TOP 1 @Sponsor = SponsorName
          FROM      @Proposals
     ELSE
          SELECT    @Sponsor = NULL

     -- Return Result Set
     SELECT    @DeliverableStatus As DeliverableStatus,
	       @Institution AS Institution,
               @Department AS Department,
               @Unit AS Unit,
               @PrincipalInvestigator AS PrincipalInvestigator,
               @OriginatingSponsor AS OriginatingSponsor,
               @Sponsor AS Sponsor,
               GroupBy,
               GroupById1,
               GroupByName1,
               GroupById2,
               GroupByName2,
               GroupByName3,
               SUM(CASE WHEN DeliverableDueDate > dateadd(dd, -1,dateadd(mm,-1,@ReportDate)) 
                    THEN DeliverableCount
                    ELSE NULL
               END) AS ThirtyDays,
               SUM(CASE WHEN DeliverableDueDate between dateadd(mm,-2,@ReportDate) and dateadd(dd, -1,dateadd(mm,-1,@ReportDate)) 
                    THEN DeliverableCount
                    ELSE NULL
               END) AS SixtyDays,
               SUM(CASE WHEN DeliverableDueDate between dateadd(mm,-3,@ReportDate) and dateadd(dd, -1,dateadd(mm,-2,@ReportDate)) 
                    THEN DeliverableCount
                    ELSE NULL
               END) AS NinetyDays,
               SUM(CASE WHEN DeliverableDueDate < dateadd(dd, -1,dateadd(mm,-3,@ReportDate)) 
                    THEN DeliverableCount
                    ELSE NULL
               END) AS MoreThanNinety
     FROM      (
               SELECT    @GroupBy AS GroupBy,
                         CASE @GroupBy 
                              WHEN 'ORG' THEN ISNULL(InstitutionId,-1)     -- -1 means not set
                              ELSE NULL
                         END AS GroupById1,
                         CASE @GroupBy 
                              WHEN 'ORG' THEN ISNULL(Institution,'UNASSIGNED')
                              ELSE NULL
                         END AS GroupByName1,
                         CASE @GroupBy 
                              WHEN 'ORG' THEN NULL  -- -1 means not set
                              ELSE -1
                         END AS GroupById2,
                         CASE @GroupBy 
                              WHEN 'ORG' THEN ISNULL(InstrumentType,'UNASSIGNED')
                              ELSE NULL
                         END AS GroupByName2,
                         CASE @GroupBy 
                              WHEN 'ORG' THEN NULL    -- -1 means not set
                              ELSE -1
                         END AS GroupByName3,
                         ReportStatus AS DeliverableStatus,
                         REPORTSTAT_CODE,
                         COUNT(ReportId) AS DeliverableCount, 
			 DeliverableDueDate
               FROM      @Proposals
               GROUP BY  CASE @GroupBy 
                              WHEN 'ORG' THEN ISNULL(InstitutionId,-1)     -- -1 means not set
                              ELSE NULL
                         END,
                         CASE @GroupBy 
                              WHEN 'ORG' THEN ISNULL(Institution,'UNASSIGNED')
                              ELSE NULL
                         END,
                         CASE @GroupBy 
                              WHEN 'ORG' THEN ISNULL(InstrumentType,'UNASSIGNED')
                              ELSE NULL
                         END,
			 ReportStatus,
             REPORTSTAT_CODE, 
			 DeliverableDueDate) DC
     GROUP BY  GroupBy,
               GroupById1,
               GroupByName1,
               GroupById2,
               GroupByName2,
               GroupByName3
     ORDER BY  GroupBy,
               GroupByName1,
               GroupByName2
               
     -- check for errors
     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error != 0 
     BEGIN
          RETURN -1
     END
      
     -- Return Success
     RETURN 0
     
END
go

GRANT EXECUTE ON dbo.RptFinancialAgingReport TO InsightReport,Insight
go


